import xlrd
import time
import datetime
import pandas as pd
import numpy as np
import pyecharts.options as opts
from pyecharts.charts import Line,Bar,Page

# 开始时间
print("startTime:"+time.strftime('%Y-%m-%d %H:%M:%S'))

# 定义处理的文件
time1=datetime.datetime.now()
time2=time1-datetime.timedelta(days=1)
xlsFileName=time2.strftime("data%Y.%m.%d.xls")
htmlFileName=time2.strftime("%Y.%m.%d.html")
# print(xlsFileName)
# xlsFileName = 'data2022.04.18.xls'
dealPath = "./"
fileDeal = dealPath+xlsFileName
print(fileDeal)

################################# readData 利用xlrd库将读取文件
class readData:
    def readXls():
        workbook = xlrd.open_workbook(fileDeal)
        table = workbook.sheets()[0]
        # 获取第一个sheet表格
        table = workbook.sheets()[0]
        # 获取行数
        rows = table.nrows
        # 获取列数
        cols = table.ncols
        # 创建rows行cols列的数组matrix
        matrix = [None]*rows
        for i in range(len(matrix)):
            matrix[i] = [0]*cols
        # 循环获取每行的数据到matrix中
        for row in range(rows):
            for col in range(cols):
                if table.cell_value(row, col) == '--':
                    matrix[row][col] = 0
                else:
                    matrix[row][col] = table.cell_value(row, col)
        #print(matrix)

        # 去掉首行标题，并选取需要的列Date、Price、callLast、call_vol(大于0)、call_open、putLast、putVol、putOpen
        dataList = []
        for row in matrix:
            if row[0] != 'Date':
                dataList.append([row[0],row[1],row[4],row[8],row[9],row[11],row[15],row[16]])
        # print(dataList)

        return dataList



################################# 程序正文

xlsData = readData.readXls()
# print(xlsData)

# 取唯一日期，并去掉首行标题
dateList = []
for i in xlsData:
    if i[0] not in dateList and i[0] != 'Date':
        dateList.append(i[0])
# print(dateList)

page = Page()
for dateDeal in dateList:
    # print(dateDeal)
    dataCall = []
    dataPut = []
    for i in xlsData:
        if i[0] == dateDeal:
            dataCall.append([i[1],i[2],i[3],i[4]])  #strike+price,vol,open
            dataPut.append([i[1],i[5],i[6],i[7]])  #the same

    pdCall = pd.DataFrame(dataCall)
    callStrike_tmp = pdCall[0].tolist()
    callStrike = []
    for i in callStrike_tmp:
        callStrike.append(str(i))
    callPrice = pdCall[1].tolist()
    callVolume = pdCall[2].tolist()
    callOpen = pdCall[3].tolist()
    # print(callStrike)
    # print(callPrice)
    # print(callVolume)
    # print(callOpen)
    bar = (
        Bar(init_opts=opts.InitOpts(width="1800px", height="300px")) # 图表大小
        .add_xaxis(callStrike)   # x轴
        .add_yaxis("当天交易量",callVolume)
        .add_yaxis("截止前一天开仓量",callOpen)
        .extend_axis(
        yaxis=opts.AxisOpts(
            axislabel_opts=opts.LabelOpts(formatter="{value} $"), interval=1, min_=-5)
        )
            .set_series_opts(label_opts=opts.LabelOpts(is_show=True))
        .set_global_opts(
            title_opts=opts.TitleOpts(title=dateDeal+" Call",title_textstyle_opts=opts.TextStyleOpts(font_size=30),pos_left='6%'),
            yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value} H")),
        )
    )
    line = Line().add_xaxis(callStrike).add_yaxis("Call价格", callPrice, yaxis_index=1)
    bar.overlap(line)

    pdPut = pd.DataFrame(dataPut)
    putStrike_tmp = pdPut[0].tolist()
    putStrike = []
    for i in putStrike_tmp:
        putStrike.append(str(i))
    putPrice = pdPut[1].tolist()
    putVolume = pdPut[2].tolist()
    putOpen = pdPut[3].tolist()
    # print(putStrike)
    # print(putPrice)
    # print(putVolume)
    # print(putOpen)
    bar1 = (
        Bar(init_opts=opts.InitOpts(width="1800px", height="300px")) # 图表大小
        .add_xaxis(putStrike)   # x轴
        .add_yaxis("当天交易量",putVolume)
        .add_yaxis("截止前一天开仓量",putOpen)
        .extend_axis(
        yaxis=opts.AxisOpts(
            axislabel_opts=opts.LabelOpts(formatter="{value} $"), interval=1, min_=-5)
        )
            .set_series_opts(label_opts=opts.LabelOpts(is_show=True))
        .set_global_opts(
            title_opts=opts.TitleOpts(title=dateDeal+" Put",title_textstyle_opts=opts.TextStyleOpts(font_size=30),pos_left='6%'),
            yaxis_opts=opts.AxisOpts(axislabel_opts=opts.LabelOpts(formatter="{value} H")),
        )
    )
    line1 = Line().add_xaxis(putStrike).add_yaxis("Put价格", putPrice, yaxis_index=1)
    bar1.overlap(line1)

    page.add(bar)
    page.add(bar1)

    # 只输出一天开关
    # break

page.render(htmlFileName)


# 结束时间
print("endTime:"+time.strftime('%Y-%m-%d %H:%M:%S'))





